--------------------------------------------------
-- Skills
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Skills]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[Skills]
	(
		ID						integer primary key IDENTITY NOT NULL,
		NAME					varchar(100) NOT NULL UNIQUE
	);
END
GO


--------------------------------------------------
-- OfficeEndPoints
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[OfficeEndPoints]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[OfficeEndPoints]
	(
		ID						integer primary key IDENTITY NOT NULL,
		SIP						varchar(200) NOT NULL UNIQUE
	);
END
GO

-- add column CHANGED_ON
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'CHANGED_ON' AND OBJECT_ID = object_id(N'[dbo].[OfficeEndPoints]'))
BEGIN
	ALTER TABLE [dbo].OfficeEndPoints ADD CHANGED_ON datetime
END
GO

--------------------------------------------------
-- Supervisors
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Supervisors]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[Supervisors]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OFFICE_ENDPOINT_ID		integer references [dbo].[OfficeEndPoints](ID) NOT NULL,
		DOMAIN_LOGON_NAME		varchar(100) NOT NULL,
		DOMAIN_OID				uniqueidentifier NOT NULL,

		CONSTRAINT Supervisors_OE_OID_unique UNIQUE(OFFICE_ENDPOINT_ID, DOMAIN_OID)
	);
END
GO


--------------------------------------------------
-- Agents
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Agents]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[Agents]
	(
		ID						integer primary key IDENTITY NOT NULL,
		OFFICE_ENDPOINT_ID		integer references [dbo].[OfficeEndPoints](ID) NOT NULL,
		DOMAIN_LOGON_NAME		varchar(100) NOT NULL,
		DOMAIN_OID				uniqueidentifier NOT NULL,

		CONSTRAINT Agents_OE_OID_unique UNIQUE(OFFICE_ENDPOINT_ID, DOMAIN_OID)
	);
END
GO


--------------------------------------------------
-- AgentSkills
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[AgentSkills]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[AgentSkills]
	(
		AGENT_ID				integer references [dbo].[Agents](ID) NOT NULL,
		SKILL_ID				integer references [dbo].[Skills](ID) NOT NULL,
		SKILL_VALUE				integer NOT NULL,

		CONSTRAINT AgentSkills_AG_SK_unique UNIQUE(AGENT_ID, SKILL_ID),
		CONSTRAINT AgentSkills_SV_check CHECK(SKILL_VALUE > 0)
	);
END
GO


--------------------------------------------------
-- CustomerEndPoints
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CustomerEndPoints]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[CustomerEndPoints]
	(
		ID						integer primary key IDENTITY NOT NULL,
		TEL						varchar(50) NOT NULL UNIQUE,
		SKILL_ID				integer references [dbo].[Skills](ID) NOT NULL,
	);
END
GO

-- add column OFFICE_ENDPOINT_ID
IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N'OFFICE_ENDPOINT_ID' AND OBJECT_ID = object_id(N'[dbo].[CustomerEndPoints]'))
BEGIN
	ALTER TABLE [dbo].[CustomerEndPoints] ADD OFFICE_ENDPOINT_ID integer references [dbo].[OfficeEndPoints](ID) NOT NULL
END
GO


--------------------------------------------------
-- AgentDistributions
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[AgentDistributions]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	CREATE TABLE [dbo].[AgentDistributions]
	(
		AGENT_ID				integer references [dbo].[Agents](ID) NOT NULL,
		CUSTOMER_ENDPOINT_ID	integer references [dbo].[CustomerEndPoints](ID) NOT NULL,

		CONSTRAINT AgentSkills_AG_CE_unique UNIQUE(AGENT_ID, CUSTOMER_ENDPOINT_ID)
	);
END
GO
